# import os
# import pandas as pd

# # 检查文件是否存在
# file_path = r'C:\Users\admin\Desktop\23软件3+2.xlsx'
# if not os.path.exists(file_path):
#     print(f"文件 {file_path} 不存在，请检查文件路径是否正确")
# else:
#     try:
#         # 读取Excel文件
#         df = pd.read_excel(file_path)
#         print("文件读取成功")
        
#         # 显示原始数据
#         print("原始数据:")
#         print(df)
        
#         # 检查列名
#         required_columns = ['非视频资源总数', '非视频资源查看数', '获得经验值', '非视频资源总经验值']
#         if not all(col in df.columns for col in required_columns):
#             missing_columns = [col for col in required_columns if col not in df.columns]
#             print(f"缺少列: {missing_columns}")
#             exit()
        
#         # 检查数据类型并处理缺失值
#         for col in required_columns:
#             if not pd.api.types.is_numeric_dtype(df[col]):
#                 print(f"列 {col} 不是数值型")
#                 df[col] = pd.to_numeric(df[col], errors='coerce')  # 将非数值型转换为NaN
            
#             # 填充缺失值为0
#             df[col].fillna(0, inplace=True)
        
#         # 1. 对学生成绩进行汇总计算
#         total_scores = df[required_columns].sum(axis=1)
#         df['总分'] = total_scores
        
#         # 2. 根据成绩对学生进行排序
#         df_sorted = df.sort_values(by='总分', ascending=False)
        
#         # 显示排序后的数据
#         print("排序后的数据:")
#         print(df_sorted)
        
#         # 将新生成的DataFrame保存到新的Excel文件
#         df_sorted.to_excel('sorted_students_scores.xlsx', index=False)
#         print("已成功保存排序后的数据到 sorted_students_scores.xlsx")
        
#     except KeyError as e:
#         print(f"列名错误: {e}")
#     except Exception as e:
#         print(f"读取文件时发生错误: {e}")



# import os
# import pandas as pd

# # 读取Excel文件
# df = pd.read_excel(r'23软件3+2.xlsx', engine='openpyxl')

# # 创建files目录
# if not os.path.exists('files'):
#     os.makedirs('files')

# # 遍历DataFrame中的每一行
# for index, row in df.iterrows():
#     student_id = str(row['学号'])
#     student_name = row['姓名']
#     total_score = row['非视频资源总数'] + row['非视频资源查看数'] + row['非视频资源总经验值'] + row['获得经验值']
    
#     # 创建学生文件夹
#     student_folder = os.path.join('files', student_id)
#     if not os.path.exists(student_folder):
#         os.makedirs(student_folder)
    
#     # 创建学生姓名的txt文件并写入总成绩
#     txt_file = os.path.join(student_folder, f'{student_name}.txt')
#     with open(txt_file, 'w', encoding='utf-8') as file:
#         file.write(f'总成绩: {total_score}\n')

# print('所有文件已成功创建并写入成绩。')



import os
import pandas as pd

# 读取Excel文件
df = pd.read_excel(r'23软件3 2.xlsx', engine='openpyxl')

# 创建files目录
if not os.path.exists('files'):
    os.makedirs('files')

# 遍历DataFrame中的每一行
for index, row in df.iterrows():
    student_id = str(row['学号'])
    student_name = row['姓名']
    # 使用正确的列名
    total_score = row['非视频资源总数'] + row['非视频资源查看数'] + row['非视频资源总经验值'] + row['获得经验值']
    
    # 创建学生文件夹
    student_folder = os.path.join('files', student_id)
    if not os.path.exists(student_folder):
        os.makedirs(student_folder)
    
    # 创建学生姓名的txt文件并写入总成绩
    txt_file = os.path.join(student_folder, f'{student_name}.txt')
    with open(txt_file, 'w', encoding='utf-8') as file:
        file.write(f'总成绩: {total_score}\n')

print('所有文件已成功创建并写入成绩。')
